# _*_ coding:utf-8 _*_
# @Time  : 2022.09.19
# @Author: zizlee
# 现货价格维护
import datetime
import pandas as pd
import numpy as np
from fastapi import APIRouter, Query
from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse
from global_constant import VARIETY

spot_price_api = APIRouter()


@spot_price_api.get('/')  # 根据日期范围，品种获取现货价格
def get_spot_price(start: str = Query(...), end: str = Query(...), v: str = Query(...)):
    start_ts = datetime_utils.auth_datetime_string(start, f='%Y%m%d', ts=True)
    end_ts = datetime_utils.auth_datetime_string(end, f='%Y%m%d', ts=True)
    variety_correct = v in VARIETY.VARIETY_NAMES.keys()
    if not all([start_ts, end_ts, variety_correct]):
        return AllResponse.validate_error(msg='参数错误')
    variety_en = v
    sql = """
        SELECT id,spot_ts,variety_en,price,src_note FROM dat_spot_price 
        WHERE spot_ts>=%s AND spot_ts<=%s AND variety_en=%s;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[start_ts, end_ts, variety_en])
    # 过滤掉数据小于等于0的
    records = list(filter(lambda x: x['price'] > 0, records))
    if len(records) > 0:
        for row in records:
            row['spot_date'] = datetime_utils.timestamp_formatter(row['spot_ts'])
    return AllResponse.operate_successfully(data=records)


@spot_price_api.get('/season/')  # 基差与现货季节性分析
def basic_spot_season(date: str = Query(...)):
    query_date = datetime_utils.auth_datetime_string(date, f='%Y%m%d')
    if not query_date:
        return AllResponse.validate_error(msg='invalid query param date!')
    query_date_obj = datetime.datetime.strptime(query_date, '%Y%m%d')
    current_year, current_month = query_date_obj.year, query_date_obj.month

    query_dominant = """
        SELECT quotes_ts,variety_en,contract,close_price 
        FROM dat_futures_price_position 
        WHERE variety_en=contract AND quotes_ts=%s;
    """
    dominant_param = [int(query_date_obj.timestamp())]
    # 查询今年以来的所有现货价格数据
    query_spot = """
        SELECT spot_ts,variety_en,price,src_note FROM dat_spot_price WHERE spot_ts>=%s AND spot_ts<=%s;
    """

    spot_start = datetime.datetime.strptime(f'{current_year}-01-01', '%Y-%m-%d')
    spot_end = datetime.datetime.strptime(f'{current_year}-12-31', '%Y-%m-%d')

    db = FAConnection()
    dominant = db.query(query_dominant, dominant_param, keep_conn=True)
    spots = db.query(query_spot, [int(spot_start.timestamp()), int(spot_end.timestamp())])
    dominant_df = pd.DataFrame(dominant)
    spots_df = pd.DataFrame(spots)

    # 将现货数据为0的改为np.nan
    spots_df['price'] = spots_df['price'].apply(lambda x: np.NAN if x == 0 else x)
    if dominant_df.empty:
        return AllResponse.operate_successfully(msg='分析期现数据成功,没有主力合约价格!', data=[])
    if spots_df.empty:
        return AllResponse.operate_successfully(msg='分析期现数据成功,没有现货价格数据!', data=[])
    dominant_df['date'] = dominant_df['quotes_ts'].apply(
        lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    spots_df['date'] = spots_df['spot_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))

    # 处理现货价的数据
    # 1 截取最大到主力合约的日期
    spots_df = spots_df[spots_df['spot_ts'] <= dominant_df.at[0, 'quotes_ts']]
    # 2 以品种分组处理出平均价开始 ----------
    # 2.1 取本月数据得本月均价
    month_start, month_end = datetime_utils.get_current_month_start_end(current_year, current_month)
    month_df = spots_df[(spots_df['spot_ts'] >= int(month_start.timestamp())) & (
            spots_df['spot_ts'] <= int(month_end.timestamp()))].copy()
    month_avg = month_df.groupby(by=['variety_en'], as_index=False)['price'].mean()
    month_avg.rename(columns={'price': 'month_avg'}, inplace=True)
    # print(month_avg)
    del month_df
    # 2.2 取本季度数据得本季度均价
    quarter_start, quarter_end = datetime_utils.get_current_quarter_start_end(query_date_obj)
    quarter_df = spots_df[(spots_df['spot_ts'] >= int(quarter_start.timestamp())) & (
            spots_df['spot_ts'] <= int(quarter_end.timestamp()))].copy()
    quarter_avg = quarter_df.groupby(by=['variety_en'], as_index=False)['price'].mean()
    quarter_avg.rename(columns={'price': 'quarter_avg'}, inplace=True)
    # 2.3 计算本年度均价(默认查询为年度数据)
    year_avg = spots_df.groupby(by=['variety_en'], as_index=False)['price'].mean()
    year_avg.rename(columns={'price': 'year_avg'}, inplace=True)
    # 2.4 合并均价数据框
    avg_df = pd.merge(year_avg, quarter_avg, on=['variety_en'], how='left')
    avg_df = pd.merge(avg_df, month_avg, on=['variety_en'], how='left')
    # 2 处理平均价结束 ------------
    # 3 取最后两日的数据计算涨跌开始 -------
    spots_ts_list = list(spots_df.groupby(by=['spot_ts']).groups)
    spots_ts_list.sort()
    ts_start, ts_end = spots_ts_list[-2:] if len(spots_ts_list) > 1 else (spots_ts_list[0], spots_ts_list[0])
    temp_zddf = spots_df[(spots_df['spot_ts'] >= ts_start) & (spots_df['spot_ts'] <= ts_end)].copy()
    temp_zddf.sort_values(by=['variety_en', 'spot_ts'], inplace=True)
    # 3.2 以品种分开计算拼合数据
    zd_df = pd.DataFrame(columns=['spot_ts', 'variety_en', 'price', 'date', 'zd_value'])
    for variety_en in temp_zddf.groupby(by=['variety_en']).groups:
        group_df = temp_zddf[temp_zddf['variety_en'] == variety_en]
        group_df['zd_value'] = group_df['price'] - group_df['price'].shift(1)
        zd_df = pd.concat([zd_df, group_df])
    zd_df = zd_df[zd_df['spot_ts'] >= dominant_df.at[0, 'quotes_ts']]
    # 4 合并数据
    ret_df = pd.merge(dominant_df, zd_df[['variety_en', 'price', 'zd_value', 'src_note']], on=['variety_en'],
                      how='left')
    ret_df = pd.merge(ret_df, avg_df, on=['variety_en'], how='left')
    # 5 计算基差
    ret_df['basis'] = ret_df['price'] - ret_df['close_price']
    # 6 保留结果小数
    ret_df['zd_value'] = ret_df['zd_value'].apply(lambda x: round(x, 2))
    ret_df['month_avg'] = ret_df['month_avg'].apply(lambda x: round(x, 2))
    ret_df['quarter_avg'] = ret_df['quarter_avg'].apply(lambda x: round(x, 2))
    ret_df['year_avg'] = ret_df['year_avg'].apply(lambda x: round(x, 2))
    ret_df['basis'] = ret_df['basis'].apply(lambda x: round(x, 2))
    ret_df.fillna('-', inplace=True)
    ret_df['variety_name'] = ret_df['variety_en'].apply(lambda x: VARIETY.VARIETY_NAMES.get(x, x))
    ret_df = ret_df[['date', 'variety_en', 'variety_name', 'close_price', 'basis', 'price', 'zd_value', 'month_avg',
                     'quarter_avg', 'year_avg', 'src_note']]

    ret_data = {item['variety_en']: item for item in ret_df.to_dict(orient='records')}
    # 处理顺序(控制品种)
    nan_spot = {col: '-' for col in ret_df.columns.tolist()}
    sorted_variety = ['NI', 'CU', 'ZN', 'PB', 'AL', 'SN', 'AU', 'AG', 'SS', 'RB', 'HC', 'BU', 'RU', 'SP', 'A', 'M', 'Y',
                      'P', 'LH', 'JD', 'C', 'CS', 'I', 'J', 'JM', 'SC', 'FU', 'LU', 'L', 'PP', 'V', 'EB', 'PG', 'EG',
                      'TA', 'PF', 'MA', 'SA', 'FG', 'UR', 'SR', 'CF', 'CY', 'AP', 'CJ', 'RM', 'OI', 'PK', 'ZC', 'SF',
                      'SM']
    rep_data = []
    for v in sorted_variety:
        rep_data.append(ret_data.get(v, nan_spot))
    return AllResponse.operate_successfully(data=rep_data)
